﻿using System;
using System.Collections.Generic;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using JigsawService.Parsers;

namespace JigsawService.DataAccess.Security
{
    public class MethodsDA : JigsawBase
    {
        APIMessage _apimessage;
        string _dbstr;

        public MethodsDA(APIMessage apiMessage)
        {
            _apimessage = apiMessage;
            _dbstr = ConfigurationManager.AppSettings["SecurityDB"].ToString();
        }


        // ********************************************************************
        // Select section *****************************************************
        // ********************************************************************
        # region Select Section (returns datatable)

        /// <summary>
        /// returns all assigned methods for specific user
        /// </summary>
        public void GetUserMethods(string userID)
        {
            _apimessage.StateMessage = "MethodsDA GetUserMethods: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "select m.ActionType, m.MethodName, m.TestParams" +
                                " from Methods m" +
                                " inner join MethodAccess ma on m.GlobalID = ma.MethodGID" +
                                " inner join RoleMember rm on ma.AppRoleGID = rm.AppRoleGID" +
                                " where rm.UserAccountGID = @UserAccountGID" +
                                " and m.RowState = 'Active' and ma.RowState = 'Active' and rm.RowState = 'Active'" +
                                " order by m.ActionType, m.MethodName";

            scmd.Parameters.Add("@UserAccountGID", SqlDbType.VarChar, 50).Value = userID;
            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// returns a list of all active APINames
        /// </summary>
        public void GetAllAPIs()
        {
            _apimessage.StateMessage = "MethodsDA GetAllAPIs: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select DISTINCT APIName" +
                                " From Methods" +
                                " Where RowState = 'Active'";

            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// returns all active methods for a given apiname
        /// </summary>
        public void GetAPIMethods(string apiName)
        {
            _apimessage.StateMessage = "MethodsDA GetAPIMethods: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select *" +
                                " From Methods" +
                                " Where RowState = 'Active'" +
                                " And APIName = @APIName" +
                                " Order By MethodName";

            scmd.Parameters.Add("@APIName", SqlDbType.VarChar, 50).Value = apiName;
            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// 
        /// </summary>
        public void GetMethodVersions(string methodID)
        {
            _apimessage.StateMessage = "MethodsDA GetMethodVersions: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select *" +
                                " From Methods" +
                                " Where GlobalID = @GlobalID" +
                                " Order By RowState ASC, GlobalDate DESC";

            scmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = methodID;
            SelectBase(scmd, _dbstr, _apimessage);
        }

        /// <summary>
        /// returns all fields for a specific methods record
        /// </summary>
        public void GetMethod(string localID)
        {
            _apimessage.StateMessage = "MethodsDA GetMethod: ";
            SqlCommand scmd = new SqlCommand();

            scmd.CommandText = "Select *" +
                                " From Methods" +
                                " Where LocalID = @LocalID";

            scmd.Parameters.Add("@LocalID", SqlDbType.VarChar, 50).Value = localID;
            SelectBase(scmd, _dbstr, _apimessage);
        }

        # endregion

        // ********************************************************************
        // Action query section ***********************************************
        // ********************************************************************
        # region Action Section (returns rows affected)

        /// <summary>
        /// Common action query to execute insert, update or delete as transaction
        /// (customized dml and parameters per database table - calls ActionBase method of JigsawBase class)
        /// </summary>
        public void MethodsAction(string actiontype, string globalid, string atype, string apiname, string methodname, string methodstate)
        {
            _apimessage.StateMessage = "MethodsDA MethodsAction: ";
            SqlCommand sqlCmd = new SqlCommand();

            // assign input parameters as values to the dml parameters
            sqlCmd.Parameters.Add("@GlobalID", SqlDbType.VarChar, 50).Value = globalid;
            sqlCmd.Parameters.Add("@Location", SqlDbType.VarChar, 50).Value = _apimessage.Location;
            sqlCmd.Parameters.Add("@ActionType", SqlDbType.VarChar, 250).Value = atype;
            sqlCmd.Parameters.Add("@APIName", SqlDbType.VarChar, 50).Value = apiname;
            sqlCmd.Parameters.Add("@MethodName", SqlDbType.VarChar, 50).Value = methodname;
            sqlCmd.Parameters.Add("@MethodState", SqlDbType.VarChar, 10).Value = methodstate;
            sqlCmd.Parameters.Add("@ModID", SqlDbType.VarChar, 50).Value = _apimessage.UserID;

            string errMsg = "MethodDA MethodAction: ";
            switch (actiontype.ToLower())
            {
                case "insert":
                    // tests for duplicate methodname and duplicate record, then inserts new record
                    sqlCmd.CommandText = "DECLARE @dupmeth INT" +
                                        " SET @dupmeth = (SELECT count(*) FROM Methods WHERE APIName = @APIName AND MethodName = @MethodName AND RowState = 'Active');" +
                                        " DECLARE @duprow INT" +
                                        " SET @duprow = (SELECT count(*) FROM Methods WHERE GlobalID = @GlobalID AND RowState = 'Active');" +
                                        " IF (@dupmeth = 0 AND @duprow = 0)" +
                                        " Insert Methods (GlobalID, Location, ActionType, APIName, MethodName, MethodState, ModID)" +
                                        " Values (@GlobalID, @Location, @ActionType, @APIName, @MethodName, @MethodState, @ModID)";
                    break;

                case "update":
                    // updates old record, then inserts new version - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE Methods SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert Methods (GlobalID, Location, ActionType, APIName, MethodName, MethodState, ModID)" +
                                        " Values (@GlobalID, @Location, @ActionType, @APIName, @MethodName, @MethodState, @ModID)";
                    break;

                case "delete":
                    // updates old record, then inserts new version with deleted state - also sets any existing duplicates to edited state
                    sqlCmd.CommandText = "UPDATE Methods SET RowState = 'Edited' WHERE GlobalID = @GlobalID AND RowState = 'Active';" +
                                        " IF (@@ROWCOUNT > 0)" +
                                        " Insert Methods (GlobalID, Location, ActionType, APIName, MethodName, MethodState, RowState, ModID)" +
                                        " Values (@GlobalID, @Location, @ActionType, @APIName, @MethodName, @MethodState, 'Deleted', @ModID)";
                    break;
            }

            // execute action query
            ActionBase(actiontype.ToLower(), sqlCmd, _dbstr, errMsg, _apimessage, globalid);
        }

        # endregion

    }
}